from odoo import api, models, fields, _
from datetime import datetime
from odoo.exceptions import ValidationError
from collections import defaultdict
from odoo.tools.float_utils import float_compare, float_round


class ReportLotQualityCheck(models.Model):
    _name = 'report.lot.quality.check'
    _auto = False
    _order = 'expiration_quantity'

    location_id = fields.Many2one('stock.location', string='Location')
    product_id = fields.Many2one('product.product', string='Product')
    default_code = fields.Char(string='Product Code', related='product_id.default_code')
    categ_id = fields.Many2one('product.category', string='Product Category', related='product_id.categ_id')
    quantity = fields.Float(string='Quantity')
    uom_id = fields.Many2one('uom.uom', string='Uom', related='product_id.uom_id')
    lot_id = fields.Many2one('stock.lot', string='Lot')
    expiration_date = fields.Datetime(string='Expiration Date')
    expiration_quantity = fields.Float(string='Expiration Quantity')
    quant_id = fields.Many2one('stock.quant', string='Stock Quant')

    def quality_check(self):
        active_ids = self._context.get('active_ids')

        # 临期质检作业
        picking_type = self.env.ref('sb_quality_check.picking_type_expired_quality')
        location_dest_id = self.env.ref('sb_quality_check.location_quality_check')
        # 1 查询是否存在临期质检调拨没有完成的
        check_picking_ids = self.env['stock.picking'].sudo().search([('picking_type_id', '=', picking_type.id), ('state', 'not in', ['done', 'cancel']),('location_dest_id', '=', location_dest_id.id)])
        check_lot_ids = check_picking_ids.mapped('move_ids').mapped('lot_ids')

        # 2 查询是质检完成，调回原位置没有完成的调拨
        r_check_picking_ids = self.env['stock.picking'].sudo().search(
            [('picking_type_id', '=', picking_type.id), ('state', 'not in', ['done', 'cancel']),
             ('location_id', '=', location_dest_id.id)])
        r_check_lot_ids = r_check_picking_ids.mapped('move_ids').mapped('lot_ids')

        # 3 查询质检完成，但没有完成的报废调拨,
        # 质检报废作业
        s_picking_type = self.env.ref('sb_quality_check.picking_type_expired_scrap_quality')
        scrap_location_dest_id = self.env['stock.location'].sudo().search([('name', '=', 'Scrap')], limit=1)
        if not location_dest_id:
            scrap_location_dest_id = self.env['stock.location'].sudo().browse(16)
        s_check_picking_ids = self.env['stock.picking'].sudo().search(
            [('picking_type_id', '=', s_picking_type.id), ('state', 'not in', ['done', 'cancel']),
             ('location_dest_id', '=', scrap_location_dest_id.id)])
        s_check_lot_ids = s_check_picking_ids.mapped('move_ids').mapped('lot_ids')
        lot_ids = check_lot_ids | r_check_lot_ids | s_check_lot_ids

        # 相同位置合并调拨
        format_records = defaultdict(list)
        records = self.browse(active_ids)

        for rec in records:
            if rec.lot_id in lot_ids:
                raise ValidationError('批次：{}, 存在未完成的质检单'.format(rec.lot_id.name))
            format_records[rec.quant_id.location_id].append(rec)

        picking_list = []
        for loc, vals in format_records.items():
            picking_val = {
                'name': picking_type.sequence_id.next_by_id(),
                'picking_type_id': picking_type.id,
                'location_id': loc.id,
                'location_dest_id': location_dest_id.id,
                'scheduled_date': fields.Datetime.now(),
                'move_ids': [],
            }
            for rec in vals:
                quant = rec.quant_id
                lot = rec.lot_id
                picking_val['move_ids'].append((0, 0, self._get_move_vals(lot, picking_type, quant)))
            if picking_val['move_ids']:
                picking_list.append(picking_val)
        if picking_list:
            pickings = self.env['stock.picking'].create(picking_list)
            for picking in pickings:
                picking.action_confirm()
            return {
                'name': _('临期质检'),
                'type': 'ir.actions.act_window',
                'view_mode': 'tree,form',
                'res_model': 'stock.picking',
                'domain': [('id', 'in', pickings.ids)],
                'context': {'create': 0},
                'target': 'current',
            }
        # for rec in self.browse(active_ids):
        #     quant = rec.quant_id
        #     lot = rec.lot_id
        #     picking_vals = {
        #         'name': picking_type.sequence_id.next_by_id(),
        #         'picking_type_id': picking_type.id,
        #         'location_id': quant.location_id.id,
        #         'location_dest_id': location_dest_id.id,
        #         'scheduled_date': fields.Datetime.now(),
        #         'move_ids': [(0, 0, self._get_move_vals(lot, picking_type, quant))],
        #     }
        #     picking = self.env['stock.picking'].create(picking_vals)
        #     picking.action_confirm()

        # if lot_ids or 'picking' in locals() and picking:
        #     # 发送用户通知
        #     message = '存在带完成的临期质检作业，请尽快完成。'
        #     # groups="stock.group_stock_manager,stock.group_stock_user"
        #     self.env['mail.message'].sudo().post_message_to_group_user(
        #         'sb_quality_check.group_quality_check_message_user', message)

    def _get_move_vals(self, lot, picking_type, quant):
        return {
            'company_id': self.env.user.company_id.id,
            'date': datetime.now(),
            'location_id': quant.location_id.id,
            'location_dest_id': picking_type.default_location_dest_id.id,
            'product_id': quant.product_id.id,
            'name': quant.product_id.display_name,
            'procure_method': 'make_to_stock',
            'product_uom': quant.product_uom_id.id,
            'product_uom_qty': quant.inventory_quantity_auto_apply,
            'lot_ids': [(0, 0, [lot.id])],
            'move_line_ids': [(0, 0, {
                'product_id': quant.product_id.id,
                'lot_id': lot.id,
                'quantity': quant.inventory_quantity_auto_apply,
                'quant_id': quant.id,
                'location_id': quant.location_id.id,
                'location_dest_id': self.env.ref('sb_quality_check.location_quality_check').id,
            })]
        }


    @property
    def _table_query(self):
        location_dest_id = self.env.ref('sb_quality_check.location_quality_check')
        return """
                WITH RECURSIVE 
                production_location AS (
                    SELECT id, ARRAY [id] AS location_path
                    FROM stock_location
                    WHERE barcode = 'WH-PREPRODUCTION'
                    
                    UNION ALL
                    
                    SELECT sl.id, le.location_path || sl.id
                    FROM production_location le
                    INNER JOIN stock_location sl ON le.id = sl.location_id
                    WHERE sl.id <> ALL (le.location_path) 
                ),
                check_location AS (
                    SELECT sl.id
                    FROM stock_location sl
                    WHERE sl.usage = 'internal'  -- 确保只包含内部库位
                      AND sl.id NOT IN (SELECT id FROM production_location)
                )
                SELECT ROW_NUMBER( ) OVER ( ) AS ID,
                    sq.id AS quant_id,
                    loc.id AS location_id,
                    pp.id AS product_id,
                    sq.quantity AS quantity,
                    lot.id AS lot_id,
                    lot.expiration_date AS expiration_date,
                    -- 1 AS expiration_quantity
                    EXTRACT(EPOCH FROM (lot.expiration_date - CURRENT_DATE)) / 86400.0 AS expiration_quantity
                    -- (lot.expiration_date - CURRENT_DATE) AS expiration_quantity
                FROM stock_quant sq
                INNER JOIN stock_location loc ON sq.location_id = loc.id
                INNER JOIN check_location cl ON sq.location_id = cl.id  -- 确保位置是内部的非生产库位
                INNER JOIN product_product pp ON sq.product_id = pp.id
                INNER JOIN stock_lot lot ON sq.lot_id = lot.id 
                    AND lot.expiration_date IS NOT NULL 
                    AND lot.name IS NOT NULL  -- 确保批次名称不为空
                WHERE sq.quantity > 0 -- AND pp.id=429
                AND loc.id != {}
                AND lot.alert_date < now()
                
        """.format(location_dest_id.id)
